﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class MessageCommandDA : IMessageCommandDA
    {
        private string CREATED_BY = "@CREATED_BY";
        private string EMAIL_SEQ_NO = "@EMAIL_SEQ_NO";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string MESSAGE_CONTENT = "@MESSAGE_CONTENT";
        private string MESSAGE_FROM = "@MESSAGE_FROM";
        private string MESSAGE_ID = "@MESSAGE_ID";
        private string MESSAGE_IS_READ = "@MESSAGE_IS_READ";
        private string MESSAGE_IS_SEND = "@MESSAGE_IS_SEND";
        private string MESSAGE_TIME = "@MESSAGE_TIME";
        private string MESSAGE_TITLE = "@MESSAGE_TITLE";
        private string MESSAGE_TO = "@MESSAGE_TO";
        private string MESSAGE_TYPE = "@MESSAGE_TYPE";
        private string ROWID = "@ROWID";
        private string SEND_TIMES = "@SEND_TIMES";
        private string SQL_CHECK_MESSAGE_COMMAND_ID_UNIQUE = " SELECT COUNT(1) FROM sysadm.MESSAGE_COMMAND WHERE MESSAGE_ID = @MESSAGE_ID ";
        private string SQL_DELETE_MESSAGE_COMMAND = " DELETE FROM sysadm.MESSAGE_COMMAND WHERE ROWID = @ROWID ";
        private string SQL_INSERT_MESSAGE_COMMAND = " INSERT INTO sysadm.MESSAGE_COMMAND ( WH_ID, MESSAGE_TYPE, MESSAGE_ID, MESSAGE_IS_SEND, MESSAGE_IS_READ, MESSAGE_FROM, MESSAGE_TO, MESSAGE_TITLE, MESSAGE_CONTENT, MESSAGE_TIME, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, SEND_TIMES, EMAIL_SEQ_NO) VALUES ( @WH_ID, @MESSAGE_TYPE, @MESSAGE_ID, @MESSAGE_IS_SEND, @MESSAGE_IS_READ, @MESSAGE_FROM, @MESSAGE_TO, @MESSAGE_TITLE, @MESSAGE_CONTENT, @MESSAGE_TIME, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(), @SEND_TIMES, @EMAIL_SEQ_NO )  ";
        private string SQL_SELECT_ALL_MESSAGE_COMMAND = " SELECT  ROWID, WH_ID, MESSAGE_TYPE, MESSAGE_ID, MESSAGE_IS_SEND, MESSAGE_IS_READ, MESSAGE_FROM, MESSAGE_TO, MESSAGE_TITLE, MESSAGE_CONTENT, MESSAGE_TIME, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, SEND_TIMES, EMAIL_SEQ_NO  FROM  sysadm.MESSAGE_COMMAND ";
        private string SQL_UPDATE_MESSAGE_COMMAND = " UPDATE sysadm.MESSAGE_COMMAND SET WH_ID = @WH_ID, MESSAGE_TYPE = @MESSAGE_TYPE, MESSAGE_ID = @MESSAGE_ID, MESSAGE_IS_SEND = @MESSAGE_IS_SEND, MESSAGE_IS_READ = @MESSAGE_IS_READ, MESSAGE_FROM = @MESSAGE_FROM, MESSAGE_TO = @MESSAGE_TO, MESSAGE_TITLE = @MESSAGE_TITLE, MESSAGE_CONTENT = @MESSAGE_CONTENT, MESSAGE_TIME = @MESSAGE_TIME, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), SEND_TIMES = @SEND_TIMES, EMAIL_SEQ_NO = @EMAIL_SEQ_NO WHERE ROWID = @ROWID  ";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckMessageCommandIDUnique(string ID, string whLoginID, PubParasInfo pubParasInfo)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MESSAGE_ID", ID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_MESSAGE_COMMAND_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckMessageCommandIDUnique(DataBase dataBase, DbTransaction tran, string ID, string whLoginID, PubParasInfo pubParasInfo)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@MESSAGE_ID", ID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_MESSAGE_COMMAND_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteMessageCommand(List<string> IDS, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < IDS.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", IDS[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_MESSAGE_COMMAND.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteMessageCommand(DataBase dataBase, DbTransaction tran, List<string> IDS, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < IDS.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", IDS[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_MESSAGE_COMMAND.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllMessageCommand(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_MESSAGE_COMMAND.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllMessageCommand(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_MESSAGE_COMMAND.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<MessageCommandInfo> GetAllMessageCommands(string whLoginID, PubParasInfo pubParasInfo)
        {
            List<MessageCommandInfo> messageCommandInfos = new List<MessageCommandInfo>();
            string sql = this.SQL_SELECT_ALL_MESSAGE_COMMAND.Replace("@_@", whLoginID);
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql))
            {
                if (reader.Read())
                {
                    DateTime CS00002;
                    MessageCommandInfo messageCommandInfo = new MessageCommandInfo((reader["ROWID"].ToString() != "") ? long.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["MESSAGE_TYPE"].ToString(), reader["MESSAGE_ID"].ToString(), reader["MESSAGE_IS_SEND"].ToString(), reader["MESSAGE_IS_READ"].ToString(), reader["MESSAGE_FROM"].ToString(), reader["MESSAGE_TO"].ToString(), reader["MESSAGE_TITLE"].ToString(), reader["MESSAGE_CONTENT"].ToString(), (reader["MESSAGE_TIME"].ToString() != "") ? DateTime.Parse(reader["MESSAGE_TIME"].ToString()) : (CS00002 = new DateTime()), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), int.Parse(reader["SEND_TIMES"].ToString()), int.Parse(reader["EMAIL_SEQ_NO"].ToString()));
                    messageCommandInfos.Add(messageCommandInfo);
                }
            }
            return messageCommandInfos;
        }

        public List<MessageCommandInfo> GetAllMessageCommands(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            List<MessageCommandInfo> messageCommandInfos = new List<MessageCommandInfo>();
            string sql = this.SQL_SELECT_ALL_MESSAGE_COMMAND.Replace("@_@", whLoginID);
            using (IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql))
            {
                if (reader.Read())
                {
                    DateTime CS00002;
                    MessageCommandInfo messageCommandInfo = new MessageCommandInfo((reader["ROWID"].ToString() != "") ? long.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["MESSAGE_TYPE"].ToString(), reader["MESSAGE_ID"].ToString(), reader["MESSAGE_IS_SEND"].ToString(), reader["MESSAGE_IS_READ"].ToString(), reader["MESSAGE_FROM"].ToString(), reader["MESSAGE_TO"].ToString(), reader["MESSAGE_TITLE"].ToString(), reader["MESSAGE_CONTENT"].ToString(), (reader["MESSAGE_TIME"].ToString() != "") ? DateTime.Parse(reader["MESSAGE_TIME"].ToString()) : (CS00002 = new DateTime()), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), int.Parse(reader["SEND_TIMES"].ToString()), int.Parse(reader["EMAIL_SEQ_NO"].ToString()));
                    messageCommandInfos.Add(messageCommandInfo);
                }
            }
            return messageCommandInfos;
        }

        public MessageCommandInfo GetMessageCommandByID(string ID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_MESSAGE_COMMAND.Replace("@_@", whLoginID) + " WHERE ROWID = @ROWID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", ID) };
            MessageCommandInfo messageCommandInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    messageCommandInfo = new MessageCommandInfo((reader["ROWID"].ToString() != "") ? long.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["MESSAGE_TYPE"].ToString(), reader["MESSAGE_ID"].ToString(), reader["MESSAGE_IS_SEND"].ToString(), reader["MESSAGE_IS_READ"].ToString(), reader["MESSAGE_FROM"].ToString(), reader["MESSAGE_TO"].ToString(), reader["MESSAGE_TITLE"].ToString(), reader["MESSAGE_CONTENT"].ToString(), (reader["MESSAGE_TIME"].ToString() != "") ? DateTime.Parse(reader["MESSAGE_TIME"].ToString()) : (CS00003 = new DateTime()), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), int.Parse(reader["SEND_TIMES"].ToString()), int.Parse(reader["EMAIL_SEQ_NO"].ToString()));
                }
            }
            return messageCommandInfo;
        }

        public MessageCommandInfo GetMessageCommandByID(DataBase dataBase, DbTransaction tran, string ID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_MESSAGE_COMMAND.Replace("@_@", whLoginID) + " WHERE ROWID = @ROWID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ROWID", ID) };
            MessageCommandInfo messageCommandInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                messageCommandInfo = new MessageCommandInfo((reader["ROWID"].ToString() != "") ? long.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["MESSAGE_TYPE"].ToString(), reader["MESSAGE_ID"].ToString(), reader["MESSAGE_IS_SEND"].ToString(), reader["MESSAGE_IS_READ"].ToString(), reader["MESSAGE_FROM"].ToString(), reader["MESSAGE_TO"].ToString(), reader["MESSAGE_TITLE"].ToString(), reader["MESSAGE_CONTENT"].ToString(), (reader["MESSAGE_TIME"].ToString() != "") ? DateTime.Parse(reader["MESSAGE_TIME"].ToString()) : (CS00003 = new DateTime()), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), int.Parse(reader["SEND_TIMES"].ToString()), int.Parse(reader["EMAIL_SEQ_NO"].ToString()));
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return messageCommandInfo;
        }

        public DataSet GetMessageCommandByQueryList(List<string> sqlWhere, MessageCommandQueryEntity messagecommandQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_MESSAGE_COMMAND.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!messagecommandQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, messagecommandQuery.CurrentPage, messagecommandQuery.PageSize, messagecommandQuery.SortField, messagecommandQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetMessageCommandByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, MessageCommandQueryEntity messagecommandQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_MESSAGE_COMMAND.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            temp = PagingHelper.GetPagingSQL(temp, messagecommandQuery.CurrentPage, messagecommandQuery.PageSize, messagecommandQuery.SortField, messagecommandQuery.SortDirection);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<MessageCommandInfo> GetMessageCommandsByQueryList(List<string> sqlWhere, MessageCommandQueryEntity messagecommandQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            List<MessageCommandInfo> messageCommandInfos = new List<MessageCommandInfo>();
            string temp = this.SQL_SELECT_ALL_MESSAGE_COMMAND.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!messagecommandQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, messagecommandQuery.CurrentPage, messagecommandQuery.PageSize, messagecommandQuery.SortField, messagecommandQuery.SortDirection);
            }
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, temp))
            {
                if (reader.Read())
                {
                    DateTime CS00002;
                    MessageCommandInfo messageCommandInfo = new MessageCommandInfo((reader["ROWID"].ToString() != "") ? long.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["MESSAGE_TYPE"].ToString(), reader["MESSAGE_ID"].ToString(), reader["MESSAGE_IS_SEND"].ToString(), reader["MESSAGE_IS_READ"].ToString(), reader["MESSAGE_FROM"].ToString(), reader["MESSAGE_TO"].ToString(), reader["MESSAGE_TITLE"].ToString(), reader["MESSAGE_CONTENT"].ToString(), (reader["MESSAGE_TIME"].ToString() != "") ? DateTime.Parse(reader["MESSAGE_TIME"].ToString()) : (CS00002 = new DateTime()), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), int.Parse(reader["SEND_TIMES"].ToString()), int.Parse(reader["EMAIL_SEQ_NO"].ToString()));
                    messageCommandInfos.Add(messageCommandInfo);
                }
            }
            return messageCommandInfos;
        }

        public List<MessageCommandInfo> GetMessageCommandsByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, MessageCommandQueryEntity messagecommandQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            List<MessageCommandInfo> messageCommandInfos = new List<MessageCommandInfo>();
            string temp = this.SQL_SELECT_ALL_MESSAGE_COMMAND.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!messagecommandQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, messagecommandQuery.CurrentPage, messagecommandQuery.PageSize, messagecommandQuery.SortField, messagecommandQuery.SortDirection);
            }
            using (IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, temp))
            {
                if (reader.Read())
                {
                    DateTime CS00002;
                    MessageCommandInfo messageCommandInfo = new MessageCommandInfo((reader["ROWID"].ToString() != "") ? long.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["MESSAGE_TYPE"].ToString(), reader["MESSAGE_ID"].ToString(), reader["MESSAGE_IS_SEND"].ToString(), reader["MESSAGE_IS_READ"].ToString(), reader["MESSAGE_FROM"].ToString(), reader["MESSAGE_TO"].ToString(), reader["MESSAGE_TITLE"].ToString(), reader["MESSAGE_CONTENT"].ToString(), (reader["MESSAGE_TIME"].ToString() != "") ? DateTime.Parse(reader["MESSAGE_TIME"].ToString()) : (CS00002 = new DateTime()), reader["IS_ACTIVE"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), int.Parse(reader["SEND_TIMES"].ToString()), int.Parse(reader["EMAIL_SEQ_NO"].ToString()));
                    messageCommandInfos.Add(messageCommandInfo);
                }
            }
            return messageCommandInfos;
        }

        public int InsertMessageCommand(MessageCommandInfo messageCommandInfo, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    messageCommandInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_MessageCommand_Parameters(messageCommandInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_MESSAGE_COMMAND.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertMessageCommand(DataBase dataBase, DbTransaction tran, MessageCommandInfo messageCommandInfo, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    messageCommandInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_MessageCommand_Parameters(messageCommandInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_MESSAGE_COMMAND.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertMessageCommands(List<MessageCommandInfo> messageCommandInfos, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    foreach (MessageCommandInfo messageCommandInfo in messageCommandInfos)
                    {
                        messageCommandInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                        SqlParameter[] paras = this.Set_MessageCommand_Parameters(messageCommandInfo);
                        if (paras != null)
                        {
                            result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_MESSAGE_COMMAND.Replace("@_@", whLoginID[i]), paras);
                        }
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertMessageCommands(DataBase dataBase, DbTransaction tran, List<MessageCommandInfo> messageCommandInfos, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    foreach (MessageCommandInfo messageCommandInfo in messageCommandInfos)
                    {
                        messageCommandInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                        SqlParameter[] paras = this.Set_MessageCommand_Parameters(messageCommandInfo);
                        if (paras != null)
                        {
                            result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_MESSAGE_COMMAND.Replace("@_@", whLoginID[i]), paras);
                        }
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_MessageCommand_Parameters(MessageCommandInfo messageCommandInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.ROWID, SqlDbType.Int), new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.MESSAGE_TYPE, SqlDbType.VarChar, 10), new SqlParameter(this.MESSAGE_ID, SqlDbType.VarChar, 100), new SqlParameter(this.MESSAGE_IS_SEND, SqlDbType.VarChar, 1), new SqlParameter(this.MESSAGE_IS_READ, SqlDbType.VarChar, 1), new SqlParameter(this.MESSAGE_FROM, SqlDbType.VarChar, 100), new SqlParameter(this.MESSAGE_TO, SqlDbType.VarChar, 0x7d0), new SqlParameter(this.MESSAGE_TITLE, SqlDbType.VarChar, 200), new SqlParameter(this.MESSAGE_CONTENT, SqlDbType.VarChar, 0x7d0), new SqlParameter(this.MESSAGE_TIME, SqlDbType.DateTime, 8), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.SEND_TIMES, SqlDbType.Int), new SqlParameter(this.EMAIL_SEQ_NO, SqlDbType.Int) };
            paramArray[0].Value = messageCommandInfo.RowID;
            if (!string.IsNullOrEmpty(messageCommandInfo.WhID))
            {
                paramArray[1].Value = messageCommandInfo.WhID;
            }
            else
            {
                paramArray[1].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.MessageType))
            {
                paramArray[2].Value = messageCommandInfo.MessageType;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.MessageID))
            {
                paramArray[3].Value = messageCommandInfo.MessageID;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.MessageIsSend))
            {
                paramArray[4].Value = messageCommandInfo.MessageIsSend;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.MessageIsRead))
            {
                paramArray[5].Value = messageCommandInfo.MessageIsRead;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.MessageFrom))
            {
                paramArray[6].Value = messageCommandInfo.MessageFrom;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.MessageTo))
            {
                paramArray[7].Value = messageCommandInfo.MessageTo;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.MessageTitle))
            {
                paramArray[8].Value = messageCommandInfo.MessageTitle;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.MessageContent))
            {
                paramArray[9].Value = messageCommandInfo.MessageContent;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (messageCommandInfo.MessageTime != new DateTime())
            {
                paramArray[10].Value = messageCommandInfo.MessageTime;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.IsActive))
            {
                paramArray[11].Value = messageCommandInfo.IsActive;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.CreatedBy))
            {
                paramArray[12].Value = messageCommandInfo.CreatedBy;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(messageCommandInfo.UpdatedBy))
            {
                paramArray[13].Value = messageCommandInfo.UpdatedBy;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            paramArray[14].Value = messageCommandInfo.SendTimes;
            paramArray[15].Value = messageCommandInfo.EmailSeqNo;
            return paramArray;
        }

        public int UpdateMessageCommand(MessageCommandInfo messageCommandInfo, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    messageCommandInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_MessageCommand_Parameters(messageCommandInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_MESSAGE_COMMAND.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateMessageCommand(DataBase dataBase, DbTransaction tran, MessageCommandInfo messageCommandInfo, List<string> whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    messageCommandInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_MessageCommand_Parameters(messageCommandInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_MESSAGE_COMMAND.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

